package com.shchuangzhi.smartguard.core;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.elvishew.xlog.XLog;

import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;


/**
 * Created by j_hu on 5/3/17.
 */

public class DBHelper extends SQLiteOpenHelper {
    public static final int DB_VERSION = 1;
    public static final String DB_NAME = "smartguard.db";
    public static final String CREATE_USER_TABLE = "CREATE TABLE user ( id INTEGER PRIMARY KEY AUTOINCREMENT, account VARCHAR (20) UNIQUE, pwd VARCHAR (50), role INT DEFAULT (1) ); ";
    public static final String ADD_ADMIN = "INSERT INTO user(account,pwd) VALUES('admin','cba3c914d5fbd2aab961086c0d82b7bb');";
    public static final String ADD_DEFAULT_USER = "INSERT INTO user(account,pwd,role) VALUES(?,'67d9347c364af144e5853241222fc8ad',2);";
    public static final String DROP_USER_TABLE = "DROP TABLE IF EXISTS user;";
    public static final String GET_ROLE = "SELECT role FROM user WHERE account = ? AND pwd = ?;";

    public static final String CREATE_SCHOOL_TABLE = "CREATE TABLE school ( id VARCHAR(50) PRIMARY KEY,  name VARCHAR(100) NOT NULL );";
    public static final String DROP_SCHOOL_TABLE = "DROP TABLE IF EXISTS school;";
    public static final String REMOVE_SCHOOL = "DELETE FROM school;";
    public static final String ADD_SCHOOL = "INSERT INTO school(id, name) VALUES(?, ?);";
    public static final String GET_SCHOOL = "SELECT id, name FROM school LIMIT 1;";

    public static final String CREATE_GRADE_TABLE = "CREATE TABLE grade (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, [no] INT UNIQUE NOT NULL, name VARCHAR (20) NOT NULL);";
    public static final String DROP_GRADE_TABLE = "DROP TABLE IF EXISTS grade;";
    public static final String REMOVE_ALL_GRADES = "DELETE FROM grade;";
    public static final String ADD_GRADE = "INSERT INTO grade(no, name) VALUES(?, ?);";
    public static final String GET_ALL_GRADES = "SELECT no, name FROM grade;";
    public static final String QUERY_GRADE_BY_NO = "SELECT no, name FROM grade WHERE no = ? LIMIT 1;";

    public static final String CREATE_CLASS_TABLE = "CREATE TABLE class (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, [no] INT UNIQUE NOT NULL, grade_no INT NOT NULL, name VARCHAR (50) NOT NULL);";
    public static final String DROP_CLASS_TABLE = "DROP TABLE IF EXISTS class;";
    public static final String CREATE_CLASS_NO_IDX = "CREATE UNIQUE INDEX idx_class_no ON class (\"no\");";
    public static final String DROP_CLASS_NO_IDX = "DROP INDEX IF EXISTS idx_class_no;";
    public static final String CREATE_CLASS_GRADE_NO_IDX = "CREATE INDEX idx_class_grade_no ON class (grade_no);";
    public static final String DROP_CLASS_GRADE_NO_IDX = "DROP INDEX IF EXISTS idx_class_grade_no;";
    public static final String GET_CLASS_COUNT_BY_NO = "SELECT COUNT(1) FROM class WHERE no = ?;";
    public static final String ADD_CLASS = "INSERT INTO class(no, grade_no, name) VALUES(?, ?, ?);";
    public static final String UPDATE_CLASS = "UPDATE class SET name = ? WHERE no = ?;";
    public static final String DELETE_CLASS = "DELETE FROM class WHERE no = ?;";

    public static final String CREATE_STUDENT_TABLE = "CREATE TABLE student ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, class_no INT NOT NULL, [no] INT NOT NULL, name VARCHAR (20) NOT NULL, birthday VARCHAR (20), img_url VARCHAR (200) );";
    public static final String DROP_STUDENT_TABLE = "DROP TABLE IF EXISTS student;";
    public static final String CREATE_STUDENT_NO_IDX = "CREATE UNIQUE INDEX idx_student_no ON student ( \"no\" ); ";
    public static final String DROP_STUDENT_NO_IDX = "DROP INDEX IF EXISTS idx_student_no;";
    public static final String CREATE_STUDENT_CLASS_NO_IDX = "CREATE INDEX idx_student_class_no ON student ( class_no ); ";
    public static final String DROP_STUDENT_CLASS_NO_IDX = "DROP INDEX IF EXISTS idx_student_class_no;";
    public static final String ADD_STUDENT = "INSERT INTO student(no, class_no, name, birthday, img_url) VALUES(?, ?, ?, ?, ?);";
    public static final String GET_STUDENT_COUNT_BY_NO = "SELECT COUNT(1) FROM student WHERE no = ?;";
    public static final String UPDATE_STUDENT = "UPDATE student SET name = ?, birthday = ?, img_url = ?, class_no = ? WHERE no = ?;";
    public static final String DELETE_STUDENT = "DELETE FROM student WHERE no = ?;";
    public static final String GET_STUDENT_BY_NO = "SELECT t1.class_no, t1.no, t1.name, t1.birthday, t1.img_url, t2.name AS class_name FROM student t1 INNER JOIN class t2 ON t1.class_no = t2.no WHERE t1.no = ?;";

    public static final String CREATE_TEACHER_TABLE = "CREATE TABLE teacher ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, [no] INT NOT NULL UNIQUE, dept_no INT NOT NULL, name VARCHAR (50) NOT NULL, img_url VARCHAR (200) );";
    public static final String DROP_TEACHER_TABLE = "DROP TABLE IF EXISTS teacher;";
    public static final String CREATE_TEACHER_NO_IDX = "CREATE UNIQUE INDEX idx_teacher_no ON teacher ( \"no\" ); ";
    public static final String DROP_TEACHER_IDX_NO = "DROP INDEX IF EXISTS idx_teacher_no;";
    public static final String CREATE_TEACHER_DEPT_NO_IDX = "CREATE INDEX idx_teacher_dep_no ON teacher ( dept_no );";
    public static final String DROP_TEACHER_DEPT_NO_IDX = "DROP INDEX IF EXISTS idx_teacher_dep_no;";
    public static final String ADD_TEACHER = "INSERT INTO teacher(no, dept_no, name, img_url) VALUES(?, ?, ?, ?);";
    public static final String GET_TEACHER_COUNT_BY_NO = "SELECT COUNT(1) FROM teacher WHERE no = ?;";
    public static final String UPDATE_TEACHER = "UPDATE teacher SET name = ?, img_url = ?, dept_no = ? WHERE no = ?;";
    public static final String DELETE_TEACHER = "DELETE FROM teacher WHERE no = ?";
    public static final String GET_TEACHER_BY_NO = "SELECT t1.dept_no, t1.no, t1.name, t1.img_url, t2.name AS dept_name FROM teacher t1 INNER JOIN class t2 ON t1.dept_no = t2.no WHERE t1.no = ?;";

    public static final String CREATE_CARD_TABLE = "CREATE TABLE card ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, [no] VARCHAR (20) NOT NULL UNIQUE, holder_no INT NOT NULL, signature VARCHAR (20) NOT NULL, seq INT NOT NULL, holder_type INT NOT NULL ); ";
    public static final String DROP_CARD_TABLE = "DROP TABLE IF EXISTS card;";
    public static final String CREATE_CARD_NO_IDX = "CREATE UNIQUE INDEX idx_card_no ON card ( \"no\" );";
    public static final String DROP_CARD_NO_IDX = "DROP INDEX IF EXISTS idx_card_no;";
    public static final String CREATE_CARD_HOLDER_NO_IDX = "CREATE INDEX idx_card_holder_no ON card ( holder_no );";
    public static final String DROP_CARD_HOLDER_NO_IDX = "DROP INDEX IF EXISTS idx_card_holder_no;";
    public static final String CREATE_CARD_HOLDER_TYPE_IDX = "CREATE INDEX idx_card_holder_type ON card ( holder_type );";
    public static final String DROP_CARD_HOLDER_TYPE_IDX = "DROP INDEX IF EXISTS idx_card_holder_type;";
    public static final String GET_CARD_BY_NO = "SELECT no, holder_no, signature, seq, holder_type FROM card WHERE no = ?;";
    public static final String GET_CARD_COUNT_BY_NO = "SELECT COUNT(1) FROM card WHERE no = ?;";
    public static final String ADD_CARD = "INSERT INTO card(no, holder_no, signature, seq, holder_type) VALUES(?, ?, ?, ?, ?);";
    public static final String UPDATE_CARD = "UPDATE card SET holder_no = ?, signature = ?, seq = ?, holder_type = ? WHERE no = ?;";
    public static final String DELETE_CARD = "DELETE FROM card WHERE no = ?;";

    public static final String CREATE_STUDENT_STATE_TABLE = "CREATE TABLE student_state ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, code INT UNIQUE NOT NULL, name VARCHAR (20) NOT NULL );";
    public static final String DROP_STUDENT_STATE_TABLE = "DROP TABLE IF EXISTS student_state;";
    public static final String REMOVE_ALL_STUDENT_STATE = "DELETE FROM student_state;";
    public static final String ADD_STUDENT_STATE = "INSERT INTO student_state(code, name) VALUES(?, ?);";
    public static final String GET_ALL_STUDENT_STATES = "SELECT code, name FROM student_state WHERE code <> 1;";
    public static final String GET_STUDENT_STATE_BY_CODE = "SELECT code, name FROM student_state WHERE code = ?;";

    public static final String CREATE_INOUTRECORD_TABLE = "CREATE TABLE inout ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, machine INT NOT NULL, [no] INT NOT NULL, name VARCHAR (50) NOT NULL, state INT NOT NULL, state_name VARCHAR (50) NOT NULL, class_name VARCHAR (50) NOT NULL, card_seq INT NOT NULL, is_manual INT NOT NULL DEFAULT (0), is_upload INT NOT NULL DEFAULT (0), create_time DATETIME NOT NULL ); ";
    public static final String DROP_INOUTRECORD_TABLE = "DROP TABLE IF EXISTS inout;";
    public static final String CREATE_INOUT_NO_IDX = "CREATE INDEX idx_inout_no ON inout ( \"no\" );";
    public static final String DROP_INOUT_NO_IDX = "DROP INDEX IF EXISTS idx_inout_no;";
    public static final String ADD_INOUTRECORD = "INSERT INTO inout(no, machine, name, state, state_name, class_name, card_seq, is_manual, create_time) values(?, ?, ?, ?, ?, ?, ?, ?, ?);";
    public static final String GET_INOUTRECORDS = "SELECT id, machine, no, name, state, state_name, class_name, card_seq, create_time, is_manual, is_upload FROM inout WHERE create_time BETWEEN '%s' AND '%s' AND %s ORDER BY id DESC LIMIT %d;";
    public static final String GET_NOT_UPLOAD_INOUTRECORDS = "SELECT id, machine, no, name, state, state_name, class_name, card_seq, create_time, is_manual, is_upload FROM inout WHERE is_upload = 0 AND create_time BETWEEN '%s' AND '%s' LIMIT 50;";
    public static final String QUERY_INOUTRECORDS = "SELECT id, machine, no, name, state, state_name, class_name, card_seq, create_time, is_manual, is_upload FROM inout WHERE create_time BETWEEN '%s' AND '%s' AND %s ORDER BY id DESC LIMIT %d;";
    public static final String SET_UPLOAD_SUCCESS = "UPDATE inout SET is_upload = 1 WHERE id = ?;";
    public static final String GET_INOUTRECORDS_COUNT = "SELECT (SELECT COUNT(1) FROM inout WHERE create_time BETWEEN '%s' AND '%s' AND %s) AS a, (SELECT COUNT(1) FROM inout WHERE create_time BETWEEN '%s' AND '%s' AND %s AND is_upload = 1) AS b;";
    public static final String GET_INOUTRECORD_DUPLICATED = "SELECT COUNT(1) FROM inout WHERE no = ? AND create_time > ? AND state = ?;";

    public static final String CREATE_SYNC_DATE_TABLE = "CREATE TABLE sync_date( id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR (20) UNIQUE, last_sync_time DATETIME);";
    public static final String DROP_SYNC_DATE_TABLE = "DROP TABLE IF EXISTS sync_date;";
    public static final String INIT_SYNC_DATE_1 = "INSERT INTO sync_date(name, last_sync_time) VALUES('school.sync', '2016-01-01 00:00:00');";
    public static final String INIT_SYNC_DATE_2 = "INSERT INTO sync_date(name, last_sync_time) VALUES('grade.sync', '2016-01-01 00:00:00');";
    public static final String INIT_SYNC_DATE_3 = "INSERT INTO sync_date(name, last_sync_time) VALUES('class.sync', '2016-01-01 00:00:00');";
    public static final String INIT_SYNC_DATE_4 = "INSERT INTO sync_date(name, last_sync_time) VALUES('student.sync', '2016-01-01 00:00:00');";
    public static final String INIT_SYNC_DATE_5 = "INSERT INTO sync_date(name, last_sync_time) VALUES('teacher.sync', '2016-01-01 00:00:00');";
    public static final String INIT_SYNC_DATE_6 = "INSERT INTO sync_date(name, last_sync_time) VALUES('card.sync', '2016-01-01 00:00:00');";
    public static final String INIT_SYNC_DATE_7 = "INSERT INTO sync_date(name, last_sync_time) VALUES('student.state.sync', '2016-01-01 00:00:00');";
    public static final String INIT_SYNC_DATE_8 = "INSERT INTO sync_date(name, last_sync_time) VALUES('teacher.state.sync', '2016-01-01 00:00:00');";
    public static final String INIT_SYNC_DATE_9 = "INSERT INTO sync_date(name, last_sync_time) VALUES('inout.stats.sync', '2016-01-01 00:00:00');";
    public static final String INIT_SYNC_DATE_10 = "INSERT INTO sync_date(name, last_sync_time) VALUES('auto.switch.time.sync', '2016-01-01 00:00:00');";
    public static final String RESET_SYNC_DATE_10 = "UPDATE sync_date SET last_sync_time = '2016-01-01 00:00:00' WHERE id = 9;";
    public static final String GET_SYNC_DATE = "SELECT name, last_sync_time FROM sync_date;";
    public static final String UPDATE_SYNC_DATE = "UPDATE sync_date SET last_sync_time = ? WHERE name = ?;";

    public static final String CREATE_STATS_TABLE = "CREATE TABLE t_stats ( id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT NOT NULL, type_id INTEGER NOT NULL, create_time DATETIME NOT NULL ); ";
    public static final String DROP_STATS_TABLE = "DROP TABLE IF EXISTS t_stats;";
    public static final String INIT_STATS_TABLE_1 = "INSERT INTO t_stats(data, type_id, create_time) values('', 1, '2016-01-01 00:00:00');";
    public static final String UPDATE_STATS = "UPDATE t_stats SET data = ?, create_time = ? WHERE type_id = ?;";
    public static final String GET_STATS = "SELECT data FROM t_stats WHERE type_id = ? AND create_time BETWEEN ? AND ?;";

    public static final String GET_META_COUNT = "SELECT (SELECT COUNT(1) FROM grade WHERE no > 0) AS g_cnt, (SELECT COUNT(1) FROM class WHERE no > 0) AS c_cnt, (SELECT COUNT(1) FROM grade WHERE no < 0) AS dept_cnt, (SELECT COUNT(1) FROM class WHERE no < 0) AS duty_cnt,(SELECT COUNT(1) FROM student) AS s_cnt, (SELECT COUNT(1) FROM teacher) AS t_cnt,(SELECT COUNT(1) FROM card WHERE holder_type = 1) AS s_card_cnt, (SELECT COUNT(1) FROM card WHERE holder_type = 2) AS t_card_cnt;";

    private Context ctx;

    private static final Object lock = new Object();

    public DBHelper(Context ctx) {
        super(ctx, DB_NAME, null, DB_VERSION);
        this.ctx = ctx;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
//        synchronized (lock) {
//            try {
//                db.execSQL(CREATE_USER_TABLE);
//                db.execSQL(ADD_ADMIN);
//                db.execSQL(ADD_DEFAULT_USER);
//                db.execSQL(CREATE_SCHOOL_TABLE);
//                db.execSQL(CREATE_GRADE_TABLE);
//                db.execSQL(CREATE_CLASS_TABLE);
//                db.execSQL(CREATE_CLASS_NO_IDX);
//                db.execSQL(CREATE_CLASS_GRADE_NO_IDX);
//                db.execSQL(CREATE_STUDENT_TABLE);
//                db.execSQL(CREATE_STUDENT_NO_IDX);
//                db.execSQL(CREATE_STUDENT_CLASS_NO_IDX);
//                db.execSQL(CREATE_TEACHER_TABLE);
//                db.execSQL(CREATE_TEACHER_NO_IDX);
//                db.execSQL(CREATE_TEACHER_DEPT_NO_IDX);
//                db.execSQL(CREATE_CARD_TABLE);
//                db.execSQL(CREATE_CARD_NO_IDX);
//                db.execSQL(CREATE_CARD_HOLDER_NO_IDX);
//                db.execSQL(CREATE_CARD_HOLDER_TYPE_IDX);
//                db.execSQL(CREATE_STUDENT_STATE_TABLE);
//                db.execSQL(CREATE_INOUTRECORD_TABLE);
//                db.execSQL(CREATE_INOUT_NO_IDX);
//                db.execSQL(CREATE_STATS_TABLE);
//                db.close();
//            } catch(Exception e) {
//                XLog.e("", e);
//            }
//        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public void reset() {
        synchronized (lock) {
            try {
                SQLiteDatabase db = getDb();
                if (db != null) {
                    db.execSQL(DROP_USER_TABLE);
                    db.execSQL(CREATE_USER_TABLE);
                    db.execSQL(ADD_ADMIN);
                    db.execSQL(DROP_SCHOOL_TABLE);
                    db.execSQL(CREATE_SCHOOL_TABLE);
                    db.execSQL(DROP_GRADE_TABLE);
                    db.execSQL(CREATE_GRADE_TABLE);
                    db.execSQL(DROP_CLASS_TABLE);
                    db.execSQL(DROP_CLASS_NO_IDX);
                    db.execSQL(DROP_CLASS_GRADE_NO_IDX);
                    db.execSQL(CREATE_CLASS_TABLE);
                    db.execSQL(CREATE_CLASS_NO_IDX);
                    db.execSQL(CREATE_CLASS_GRADE_NO_IDX);
                    db.execSQL(DROP_STUDENT_TABLE);
                    db.execSQL(DROP_STUDENT_NO_IDX);
                    db.execSQL(DROP_STUDENT_CLASS_NO_IDX);
                    db.execSQL(CREATE_STUDENT_TABLE);
                    db.execSQL(CREATE_STUDENT_NO_IDX);
                    db.execSQL(CREATE_STUDENT_CLASS_NO_IDX);
                    db.execSQL(DROP_TEACHER_TABLE);
                    db.execSQL(DROP_TEACHER_IDX_NO);
                    db.execSQL(DROP_TEACHER_DEPT_NO_IDX);
                    db.execSQL(CREATE_TEACHER_TABLE);
                    db.execSQL(CREATE_TEACHER_NO_IDX);
                    db.execSQL(CREATE_TEACHER_DEPT_NO_IDX);
                    db.execSQL(DROP_CARD_TABLE);
                    db.execSQL(DROP_CARD_NO_IDX);
                    db.execSQL(DROP_CARD_HOLDER_NO_IDX);
                    db.execSQL(DROP_CARD_HOLDER_TYPE_IDX);
                    db.execSQL(CREATE_CARD_TABLE);
                    db.execSQL(CREATE_CARD_NO_IDX);
                    db.execSQL(CREATE_CARD_HOLDER_NO_IDX);
                    db.execSQL(CREATE_CARD_HOLDER_TYPE_IDX);
                    db.execSQL(DROP_STUDENT_STATE_TABLE);
                    db.execSQL(CREATE_STUDENT_STATE_TABLE);
                    db.execSQL(DROP_INOUTRECORD_TABLE);
                    db.execSQL(DROP_INOUT_NO_IDX);
                    db.execSQL(CREATE_INOUTRECORD_TABLE);
                    db.execSQL(CREATE_INOUT_NO_IDX);
                    db.execSQL(DROP_SYNC_DATE_TABLE);
                    db.execSQL(CREATE_SYNC_DATE_TABLE);
                    db.execSQL(INIT_SYNC_DATE_1);
                    db.execSQL(INIT_SYNC_DATE_2);
                    db.execSQL(INIT_SYNC_DATE_3);
                    db.execSQL(INIT_SYNC_DATE_4);
                    db.execSQL(INIT_SYNC_DATE_5);
                    db.execSQL(INIT_SYNC_DATE_6);
                    db.execSQL(INIT_SYNC_DATE_7);
                    db.execSQL(INIT_SYNC_DATE_8);
                    db.execSQL(INIT_SYNC_DATE_9);
                    db.execSQL(INIT_SYNC_DATE_10);
                    db.execSQL(DROP_STATS_TABLE);
                    db.execSQL(CREATE_STATS_TABLE);
                    db.execSQL(INIT_STATS_TABLE_1);

                    addDefaultUser(db);
                    db.close();

                    SyncRecord.getInstance().empty();
                }
            } catch(Exception e) {
                XLog.e("", e);
            }
        }
    }

    public int getRole(String account, String pwd) {
        synchronized (lock) {
            int roleId = -1;
            Cursor c = null;
            SQLiteDatabase db = null;
            try {
                db = getDb();
                if (db != null) {
                    c = db.rawQuery(GET_ROLE, new String[]{account, pwd});
                    while(c.moveToNext()) {
                        roleId = c.getInt(c.getColumnIndex("role"));
                    }
                }
            }catch(Exception e) {
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return roleId;
        }
    }

    public boolean resetSchool(School school) {
        synchronized (lock) {
            boolean ok = true;
            SQLiteDatabase db = null;
            try {
                db = getDb();
                if (db != null) {
                    db.execSQL(REMOVE_SCHOOL);
                    db.execSQL(ADD_SCHOOL, new Object[]{school.getId(), school.getName()});
                }
            }catch(Exception e) {
                ok = false;
                XLog.e("", e);
            }
            finally {
                if (db != null) {
                    db.close();
                }
            }
            return ok;
        }
    }

    public School getSchool() {
        synchronized (lock) {
            Cursor c = null;
            SQLiteDatabase db = null;
            School school = new School(this);
            try {
                db = getDb();
                if (db != null) {
                    c = db.rawQuery(GET_SCHOOL, null);
                    while(c.moveToNext()) {
                        school.setId(c.getString(c.getColumnIndex("id")));
                        school.setName(c.getString(c.getColumnIndex("name")));
                    }
                }
            }catch (Exception e) {
                XLog.e("", e);
                school = null;
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return school;
        }
    }

    public boolean groupAddGrades(ArrayList<Grade> list) {
        synchronized (lock) {
            boolean ok = true;
            SQLiteDatabase db = getDb();
            if (db != null) {
                db.beginTransaction();
                try {
                    db.execSQL(REMOVE_ALL_GRADES);
                    for(Grade g:list) {
                        db.execSQL(ADD_GRADE, new Object[]{g.getNo(), g.getName()});
                    }
                    db.setTransactionSuccessful();
                } catch(Exception e) {
                    ok = false;
                    XLog.e("", e);
                } finally {
                    db.endTransaction();
                    db.close();
                }
            }
            return ok;
        }
    }

    public List<Grade> getAllGrades() {
        synchronized (lock) {
            Cursor c = null;
            SQLiteDatabase db = null;
            List<Grade> list = new ArrayList<>();
            try {
                db = getDb();
                if (db != null) {
                    c = db.rawQuery(GET_ALL_GRADES, null);
                    while(c.moveToNext()) {
                        Grade grade = new Grade(this);
                        grade.setNO(c.getInt(c.getColumnIndex("no")));
                        grade.setName(c.getString(c.getColumnIndex("name")));
                        list.add(grade);
                    }
                }
            }catch (Exception e) {
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return list;
        }
    }

    public Grade getGradeByNo(Integer no) {
        synchronized (lock) {
            Cursor c = null;
            SQLiteDatabase db = null;
            Grade grade = new Grade(this);
            try {
                db = getDb();
                if (db != null) {
                    c = db.rawQuery(QUERY_GRADE_BY_NO, new String[]{no.toString()});
                    while(c.moveToNext()) {
                        grade.setNO(c.getInt(c.getColumnIndex("no")));
                        grade.setName(c.getString(c.getColumnIndex("name")));
                    }
                }
            }catch (Exception e) {
                XLog.e("", e);
                grade = null;
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return grade;
        }
    }

    public boolean groupAddClazz(ArrayList<Clazz> list) {
        synchronized (lock) {
            boolean ok = true;
            Cursor c = null;
            SQLiteDatabase db = null;
            try {
                db = getDb();
                if (db != null) {
                    for(Clazz clazz:list) {
                        if (clazz.isDeleted()) {
                            db.execSQL(DELETE_CLASS, new Object[]{clazz.getNo()});
                        } else {
                            c = db.rawQuery(GET_CLASS_COUNT_BY_NO, new String[]{new Integer(clazz.getNo()).toString()});
                            if (c.moveToFirst()) {
                                if (c.getInt(0) != 0) {
                                    db.execSQL(UPDATE_CLASS, new Object[]{clazz.getName(), clazz.getNo()});
                                } else {
                                    db.execSQL(ADD_CLASS, new Object[]{clazz.getNo(), clazz.getGradeNo(), clazz.getName()});
                                }
                            } else {
                                db.execSQL(ADD_CLASS, new Object[]{clazz.getNo(), clazz.getGradeNo(), clazz.getName()});
                            }
                            c.close();
                        }
                    }
                }
            } catch(Exception e) {
                ok = false;
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return ok;
        }
    }

    public boolean groupAddStudents(ArrayList<Student> list) {
        synchronized (lock) {
            boolean ok = true;
            Cursor c = null;
            SQLiteDatabase db = null;
            try {
                db = getDb();
                if (db != null) {
                    for(Student student:list) {
                        c = db.rawQuery(GET_STUDENT_COUNT_BY_NO, new String[]{new Integer(student.getNo()).toString()});
                        if (c.moveToFirst()) {
                            if (c.getInt(0) != 0) {
                                if (!student.isDeleted()) {
                                    db.execSQL(UPDATE_STUDENT, new Object[]{student.getName(), student.getBirthday(),  student.getImgURL(), student.getClassNo(), student.getNo()});
                                } else {
                                    db.execSQL(DELETE_STUDENT, new Object[]{student.getNo()});
                                }
                            } else {
                                if (!student.isDeleted()) {
                                    db.execSQL(ADD_STUDENT, new Object[]{student.getNo(), student.getClassNo(), student.getName(), student.getBirthday(), student.getImgURL()});
                                }
                            }
                        } else {
                            if (!student.isDeleted()) {
                                db.execSQL(ADD_STUDENT, new Object[]{student.getNo(), student.getClassNo(), student.getName(), student.getBirthday(), student.getImgURL()});
                            }
                        }
                        c.close();
                    }
                }
            } catch(Exception e) {
                ok = false;
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return ok;
        }
    }

    public Swiper getSwiperByNo(int no) {
        Swiper swiper = null;
        if (no / 1000 < 9) {
            swiper = getStudentByNo(no);
        } else {
            swiper = getTeacherByNo(no);
        }
        return swiper;
    }

    private Student getStudentByNo(int no) {
        synchronized (lock) {
            Cursor c = null;
            SQLiteDatabase db = null;
            Student student = null;
            try {
                db = getDb();
                if (db != null) {
                    c = db.rawQuery(GET_STUDENT_BY_NO, new String[]{new Integer(no).toString()});
                    while (c.moveToNext()) {
                        student = new Student(this);
                        student.setNo(c.getInt(c.getColumnIndex("no")));
                        student.setBirthday(c.getString(c.getColumnIndex("birthday")));
                        student.setImgURL(c.getString(c.getColumnIndex("img_url")));
                        student.setName(c.getString(c.getColumnIndex("name")));
                        student.setClassNo(c.getInt(c.getColumnIndex("class_no")));
                        student.setClassName(c.getString(c.getColumnIndex("class_name")));
                    }
                }
            } catch(Exception e) {
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return student;
        }
    }

    public Swiper getSwiperByCard(String cardNo) {
        synchronized (lock) {
            Cursor c = null;
            Swiper swiper = null;
            SQLiteDatabase db = null;
            try {
                db = getDb();
                if (db != null) {
                    Card card = null;
                    c = db.rawQuery(GET_CARD_BY_NO, new String[]{cardNo});
                    while (c.moveToNext()) {
                        card = new Card();
                        card.setNo(c.getString(c.getColumnIndex("no")));
                        card.setHolderNo(c.getInt(c.getColumnIndex("holder_no")));
                        card.setSeq(c.getInt(c.getColumnIndex("seq")));
                        card.setSignature(c.getString(c.getColumnIndex("signature")));
                        card.setHolderType(c.getInt(c.getColumnIndex("holder_type")));
                    }
                    if (card != null) {
                        swiper = card.getHolderType() == 1 ?
                                getStudentByNo(card.getHolderNo()) : getTeacherByNo(card.getHolderNo());
                        swiper.setBindCard(card);
                    }
                }
            } catch(Exception e) {
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }

            return swiper;
        }
    }

    public boolean groupAddTeachers(ArrayList<Teacher> list) {
        synchronized (lock) {
            boolean ok = true;
            Cursor c = null;
            SQLiteDatabase db = null;
            try {
                db = getDb();
                if (db != null) {
                    for(Teacher teacher:list) {
                        c = db.rawQuery(GET_TEACHER_COUNT_BY_NO, new String[]{new Integer(teacher.getNo()).toString()});
                        if (c.moveToFirst()) {
                            if (c.getInt(0) != 0) {
                                if (!teacher.isDeleted()) {
                                    db.execSQL(UPDATE_TEACHER, new Object[]{teacher.getName(), teacher.getImgURL(), teacher.getDeptNo(), teacher.getNo()});
                                } else {
                                    db.execSQL(DELETE_TEACHER, new Object[]{teacher.getNo()});
                                }
                            } else {
                                if (!teacher.isDeleted()) {
                                    db.execSQL(ADD_TEACHER, new Object[]{teacher.getNo(), teacher.getDeptNo(), teacher.getName(), teacher.getImgURL()});
                                }
                            }
                        } else {
                            if (!teacher.isDeleted()) {
                                db.execSQL(ADD_TEACHER, new Object[]{teacher.getNo(), teacher.getDeptNo(), teacher.getName(), teacher.getImgURL()});
                            }
                        }
                        c.close();
                    }
                }
            } catch(Exception e) {
                ok = false;
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return ok;
        }
    }

    private Teacher getTeacherByNo(int no) {
        synchronized (lock) {
            Cursor c = null;
            Teacher teacher = null;
            SQLiteDatabase db = null;
            try {
                db = getDb();
                if (db != null) {
                    c = db.rawQuery(GET_TEACHER_BY_NO, new String[]{new Integer(no).toString()});
                    while (c.moveToNext()) {
                        teacher = new Teacher(this);
                        teacher.setNo(c.getInt(c.getColumnIndex("no")));
                        teacher.setImgURL(c.getString(c.getColumnIndex("img_url")));
                        teacher.setName(c.getString(c.getColumnIndex("name")));
                        teacher.setDeptNo(c.getInt(c.getColumnIndex("dept_no")));
                        teacher.setDeptName(c.getString(c.getColumnIndex("dept_name")));
                    }
                }
            } catch(Exception e) {
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return teacher;
        }
    }

    public boolean groupAddCards(ArrayList<Card> list) {
        synchronized (lock) {
            Cursor c = null;
            boolean ok = true;
            SQLiteDatabase db = null;
            try {
                db = getDb();
                if (db != null) {
                    for(Card card:list) {
                        if (card.isDeleted()) {
                            db.execSQL(DELETE_CARD, new Object[]{card.getNo()});
                        } else {
                            c = db.rawQuery(GET_CARD_COUNT_BY_NO, new String[]{card.getNo()});
                            if (c.moveToFirst()) {
                                if (c.getInt(0) != 0) {
                                    db.execSQL(UPDATE_CARD, new Object[]{card.getHolderNo(), card.getSignature(), card.getSeq(), card.getHolderType(), card.getNo()});
                                } else {
                                    db.execSQL(ADD_CARD, new Object[]{card.getNo(), card.getHolderNo(), card.getSignature(), card.getSeq(), card.getHolderType()});
                                }
                            } else {
                                db.execSQL(ADD_CARD, new Object[]{card.getNo(), card.getHolderNo(), card.getSignature(), card.getSeq(), card.getHolderType()});
                            }
                            c.close();
                        }
                    }
                }
            } catch(Exception e) {
                ok = false;
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return ok;
        }
    }

    public boolean groupAddStudentStates(ArrayList<StudentState> list) {
        synchronized (lock) {
            boolean ok = true;
            SQLiteDatabase db = getDb();
            if (db != null) {
                db.beginTransaction();
                try {
                    db.execSQL(REMOVE_ALL_STUDENT_STATE);
                    for(StudentState s:list) {
                        db.execSQL(ADD_STUDENT_STATE, new Object[]{s.getCode(), s.getName()});
                    }
                    db.setTransactionSuccessful();
                } catch(Exception e) {
                    ok = false;
                    XLog.e("", e);
                } finally {
                    db.endTransaction();
                    db.close();
                }
            }
            return ok;
        }
    }

    public ArrayList<StudentState> getAllStudentStates() {
        synchronized (lock) {
            Cursor c = null;
            SQLiteDatabase db = null;
            ArrayList<StudentState> list = new ArrayList<>();
            try {
                db = getDb();
                if (db != null) {
                    c = db.rawQuery(GET_ALL_STUDENT_STATES, null);
                    while (c.moveToNext()) {
                        StudentState state = new StudentState();
                        state.setCode(c.getInt(c.getColumnIndex("code")));
                        state.setName(c.getString(c.getColumnIndex("name")));
                        list.add(state);
                    }
                }
            } catch(Exception e) {
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return list;
        }
    }

    public String getStudentStateNameByCode(int code) {
        synchronized (lock) {
            Cursor c = null;
            String stateName = "";
            SQLiteDatabase db = null;
            try {
                db = getDb();
                if (db != null) {
                    c = db.rawQuery(GET_STUDENT_STATE_BY_CODE, new String[]{new Integer(code).toString()});
                    while (c.moveToNext()) {
                        stateName = c.getString(c.getColumnIndex("name"));
                    }
                }
            } catch(Exception e) {
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return stateName;
        }
    }

    public boolean addInOut(InOutRecord rec) {
        synchronized (lock) {
            Cursor c = null;
            boolean ok = false;
            SQLiteDatabase db = null;
            try {
                boolean duplicated = false;
                db = getDb();

                if (rec.getState() == Consts.STATE_HEALTH || rec.getState() == Consts.STATE_OUT) {
                    Calendar calendar = Calendar.getInstance();
                    calendar.setTime(Clock.now());
                    calendar.add(Calendar.MINUTE, -30);
                    SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    c = db.rawQuery(GET_INOUTRECORD_DUPLICATED, new String[]{
                            new Integer(rec.getNo()).toString(),
                            sdf.format(calendar.getTime()),
                            new Integer(rec.getState()).toString()});
                    if (c.moveToFirst()) {
                        duplicated = c.getInt(0) > 0;
                    }
                }

                if (!duplicated) {
                    if (db != null) {
                        db.execSQL(ADD_INOUTRECORD,
                                new Object[]{rec.getNo(), rec.getMachine(), rec.getName(), rec.getState(), rec.getStateName(), rec.getClassName(), rec.getSeq(), rec.isManual() ? 1 : 0, rec.getTimeStr()});
                        ok = true;
                    }
                }
            }catch(Exception e) {
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return ok;
        }
    }

    public int[] getInOutRecordsCount(int mode) {
        synchronized (lock) {
            Cursor c = null;
            SQLiteDatabase db = null;
            int[] count = new int[]{0, 0};
            try {
                db = getDb();
                if (db != null) {
                    String sql = GET_INOUTRECORDS_COUNT;
                    String timeStart = new SimpleDateFormat("yyyy-MM-dd 00:00:00").format(Clock.now());
                    String timeEnd = new SimpleDateFormat("yyyy-MM-dd 23:59:59").format(Clock.now());
                    if (mode == Consts.IN_MODE) {
                        sql = String.format(sql, timeStart, timeEnd, "state <> 1", timeStart, timeEnd, "state <> 1");
                    } else {
                        sql = String.format(sql, timeStart, timeEnd, "state = 1", timeStart, timeEnd, "state = 1");
                    }

                    c = db.rawQuery(sql, null);
                    if (c.moveToFirst()) {
                        count = new int[]{c.getInt(c.getColumnIndex("a")), c.getInt(c.getColumnIndex("b"))};
                    }
                }
            } catch(Exception e) {
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return count;
        }
    }

    public List<InOutRecord> getInOutRecords(int mode) {
        return getInOutRecords(mode, Consts.MAX_IN_OUT_LIST_SIZE);
    }

    public List<InOutRecord> getInOutRecords(int mode, int count) {
        synchronized (lock) {
            Cursor c = null;
            SQLiteDatabase db = null;
            ArrayList<InOutRecord> list = new ArrayList<>();
            try {
                db = getDb();
                if (db != null) {
                    String sql = GET_INOUTRECORDS;
                    String timeStart = new SimpleDateFormat("yyyy-MM-dd 00:00:00").format(Clock.now());
                    String timeEnd = new SimpleDateFormat("yyyy-MM-dd 23:59:59").format(Clock.now());
                    if (mode == Consts.IN_MODE) {
                        sql = String.format(sql, timeStart, timeEnd,  "state <> 1", count);
                    } else {
                        sql = String.format(sql, timeStart, timeEnd, "state = 1", count);
                    }

                    c = db.rawQuery(sql, null);
                    while (c.moveToNext()) {
                        InOutRecord rec = new InOutRecord(
                                c.getInt(c.getColumnIndex("id")),
                                c.getInt(c.getColumnIndex("machine")),
                                c.getInt(c.getColumnIndex("no")),
                                c.getInt(c.getColumnIndex("card_seq")),
                                c.getInt(c.getColumnIndex("state")),
                                c.getInt(c.getColumnIndex("is_manual")) == 0 ? false : true,
                                c.getString(c.getColumnIndex("name")),
                                c.getString(c.getColumnIndex("state_name")),
                                c.getString(c.getColumnIndex("class_name")),
                                c.getString(c.getColumnIndex("create_time")));
                        list.add(rec);
                    }
                }
            } catch(Exception e) {
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return list;
        }
    }

    public List<InOutRecord> queryInOutRecords(int mode, String cond) {
        synchronized (lock) {
            int no = 0;
            try {
                if (cond.length() == 4) {
                    no = Integer.parseInt(cond);
                }
            } catch(Exception e) {}

            String sql = null;
            String timeStart = new SimpleDateFormat("yyyy-MM-dd 00:00:00").format(Clock.now());
            String timeEnd = new SimpleDateFormat("yyyy-MM-dd 23:59:59").format(Clock.now());
            if (no != 0) {
                if (mode == Consts.IN_MODE) {
                    sql = String.format(QUERY_INOUTRECORDS, timeStart, timeEnd, String.format("no = %d AND state <> 1", no), Consts.MAX_IN_OUT_LIST_SIZE);
                } else {
                    sql = String.format(QUERY_INOUTRECORDS, timeStart, timeEnd, String.format("no = %d AND state = 1", no), Consts.MAX_IN_OUT_LIST_SIZE);
                }

            } else {
                if (mode == Consts.IN_MODE) {
                    sql = String.format(QUERY_INOUTRECORDS, timeStart, timeEnd, "state <> 1 AND name LIKE '" + cond + "%'", Consts.MAX_IN_OUT_LIST_SIZE);
                } else {
                    sql = String.format(QUERY_INOUTRECORDS, timeStart, timeEnd, "state = 1 AND name LIKE '" + cond + "%'", Consts.MAX_IN_OUT_LIST_SIZE);
                }

            }

            Cursor c = null;
            SQLiteDatabase db = null;
            ArrayList<InOutRecord> list = new ArrayList<>();
            try {
                db = getDb();
                if (db != null) {
                    c = db.rawQuery(sql, null);
                    while (c.moveToNext()) {
                        InOutRecord rec = new InOutRecord(
                                c.getInt(c.getColumnIndex("id")),
                                c.getInt(c.getColumnIndex("machine")),
                                c.getInt(c.getColumnIndex("no")),
                                c.getInt(c.getColumnIndex("card_seq")),
                                c.getInt(c.getColumnIndex("state")),
                                c.getInt(c.getColumnIndex("is_manual")) == 0 ? false : true,
                                c.getString(c.getColumnIndex("name")),
                                c.getString(c.getColumnIndex("state_name")),
                                c.getString(c.getColumnIndex("class_name")),
                                c.getString(c.getColumnIndex("create_time")));
                        list.add(rec);
                    }
                }
            } catch(Exception e) {
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return list;
        }
    }

    public List<InOutRecord> getNotUploadedRecords() {
        synchronized (lock) {
            Cursor c = null;
            SQLiteDatabase db = null;
            ArrayList<InOutRecord> list = new ArrayList<>();
            try {
                db = getDb();
                if (db != null) {
                    String timeStart = new SimpleDateFormat("yyyy-MM-dd 00:00:00").format(Clock.now());
                    String timeEnd = new SimpleDateFormat("yyyy-MM-dd 23:59:59").format(Clock.now());
                    c = db.rawQuery(String.format(GET_NOT_UPLOAD_INOUTRECORDS, timeStart, timeEnd) , null);
                    while (c.moveToNext()) {
                        InOutRecord rec = new InOutRecord(
                                c.getInt(c.getColumnIndex("id")),
                                c.getInt(c.getColumnIndex("machine")),
                                c.getInt(c.getColumnIndex("no")),
                                c.getInt(c.getColumnIndex("card_seq")),
                                c.getInt(c.getColumnIndex("state")),
                                c.getInt(c.getColumnIndex("is_manual")) == 0 ? false : true,
                                c.getString(c.getColumnIndex("name")),
                                c.getString(c.getColumnIndex("state_name")),
                                c.getString(c.getColumnIndex("class_name")),
                                c.getString(c.getColumnIndex("create_time")));
                        list.add(rec);
                    }
                }
            } catch(Exception e) {
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return list;
        }
    }

    public boolean setUploadSuccess(List<Integer> list) {
        synchronized (lock) {
            boolean ok = true;
            if (list.size() == 0) {
                return false;
            }

            SQLiteDatabase db = getDb();
            if (db != null) {
                db.beginTransaction();
                try {
                    for(int id:list) {
                        db.execSQL(SET_UPLOAD_SUCCESS, new Object[]{id});
                    }
                    db.setTransactionSuccessful();
                } catch(Exception e) {
                    ok = false;
                    XLog.e("", e);
                } finally {
                    db.endTransaction();
                    db.close();
                }
            }
            return ok;
        }
    }

    public SyncRecord getSyncRecord() {
        synchronized (lock) {
            Cursor c = null;
            SQLiteDatabase db = null;
            SyncRecord record = SyncRecord.getInstance().with(this);
            if (record.isEmpty()) {
                try {
                    db = getDb();
                    if (db != null) {
                        c = db.rawQuery(GET_SYNC_DATE, null);
                        while (c.moveToNext()) {
                            record.add(c.getString(c.getColumnIndex("name")), c.getString(c.getColumnIndex("last_sync_time")));
                        }
                    }
                } catch(Exception e) {
                    XLog.e("", e);
                }
                finally {
                    if (c != null) {
                        c.close();
                    }
                    if (db != null) {
                        db.close();
                    }
                }
            }
            return record;
        }
    }

    public boolean updateSyncRecord(String name, String lastMetaUpdateDate) {
        synchronized (lock) {
            boolean ok = false;
            SQLiteDatabase db = null;
            try {
                db = getDb();
                if (db != null) {
                    db.execSQL(UPDATE_SYNC_DATE, new Object[]{lastMetaUpdateDate, name});
                    ok = true;
                }
            } catch(Exception e) {
                XLog.e("", e);
            }
            finally {
                if (db != null) {
                    db.close();
                }
            }
            return ok;
        }
    }

    public boolean updateStats(int type, String data, String date) {
        synchronized (lock) {
            boolean ok = true;
            SQLiteDatabase db = null;
            try {
                db = getDb();
                if (db != null) {
                    db.execSQL(UPDATE_STATS, new Object[]{data, date, type});
                }
            } catch(Exception e) {
                ok = false;
                XLog.e("", e);
            }
            finally {
                if (db != null) {
                    db.close();
                }
            }
            return ok;
        }
    }

    public void forceStatsSync() {
        synchronized (lock) {
            SQLiteDatabase db = null;
            try {
                db = getDb();
                if (db != null) {
                    db.execSQL(RESET_SYNC_DATE_10);
                    SyncRecord.getInstance().empty();
                }
            } catch(Exception e) {
                XLog.e("", e);
            }
            finally {
                if (db != null) {
                    db.close();
                }
            }
        }
    }

    public String getStats(int type) {
        synchronized (lock) {
            String stats = "";
            Cursor c = null;
            SQLiteDatabase db = null;
            try {
                db = getDb();
                if (db != null) {
                    c = db.rawQuery(GET_STATS, new String[]{
                            new Integer(type).toString(),
                            Clock.nowStr("yyyy-MM-dd 00:00:00"),
                            Clock.nowStr("yyyy-MM-dd 23:59:59")
                    });
                    if (c.moveToFirst()) {
                        stats = c.getString(0);
                    }
                }
            }catch(Exception e) {
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
            return stats;
        }
    }

    public int[] getMetaCountArray() {
        int[] array = new int[8];
        synchronized (lock) {
            Cursor c = null;
            SQLiteDatabase db = null;
            try {
                db = getDb();
                if (db != null) {
                    c = db.rawQuery(GET_META_COUNT, null);
                    if (c.moveToFirst()) {
                        array[0] = c.getInt(0);
                        array[1] = c.getInt(1);
                        array[2] = c.getInt(2);
                        array[3] = c.getInt(3);
                        array[4] = c.getInt(4);
                        array[5] = c.getInt(5);
                        array[6] = c.getInt(6);
                        array[7] = c.getInt(7);
                    }
                }
            }catch(Exception e) {
                XLog.e("", e);
            }
            finally {
                if (c != null) {
                    c.close();
                }
                if (db != null) {
                    db.close();
                }
            }
        }
        return array;
    }

    public DumpSummary dump() {
        SQLiteDatabase db = getDb();
        DumpSummary summary = DumpSummary.zero();
        if (db != null) {
            try {
                FileInputStream input = new FileInputStream(new File(db.getPath()));
                int read = 0;
                byte[] bytes = new byte[1024];
                byte[] target = null;
                while ((read = input.read(bytes)) != -1) {
                    target = Util.append(target, target == null ? -1 : target.length, bytes, read);
                }
                summary = new DumpSummary(target);
            } catch(Exception e) {
                XLog.e("", e);
            }
            finally {
                db.close();
            }
        }

        return summary;
    }

    private SQLiteDatabase getDb() {
        SQLiteDatabase db = null;
        try {
            db = ctx.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
        }catch(Exception e) {
            XLog.e("", e);
        }
        return db;
    }

    private void addDefaultUser(SQLiteDatabase db) {
        for(int i = 9101; i <= 9299; i++) {
            db.execSQL(ADD_DEFAULT_USER, new Object[]{i});
        }
    }

    public static class DumpSummary {
        private String crc;
        private byte[] binary;
        private byte[] salt = "**#%".getBytes();

        public DumpSummary(byte[] binary) {
            this.binary = binary;
            if (size() != 0) {
                crc = Util.getMD5(Util.append(binary, binary.length, salt, salt.length));
            }
        }

        public int size() {
            int size = 0;
            if (binary != null) {
                size = binary.length;
            }
            return size;
        }

        public String crc() {
            return crc;
        }

        public byte[] binary() {
            return binary;
        }

        public static DumpSummary zero() {
            return new DumpSummary(null);
        }
    }
}
